package jnpf.database.sql.enums;

import jnpf.database.constant.DbAliasConst;
import jnpf.database.datatype.db.interfaces.DtInterface;
import jnpf.database.enums.DbAliasEnum;
import jnpf.database.enums.ParamEnum;
import jnpf.database.source.DbBase;
import jnpf.database.sql.enums.base.SqlFrameBase;
import jnpf.database.sql.model.DbStruct;
import lombok.AllArgsConstructor;
import lombok.Getter;

import java.util.List;

/**
 * Oracle SQL语句模板
 *
 * @author JNPF开发平台组 YanYu
 * @version V3.3
 * @copyright 引迈信息技术有限公司
 * @date 2022/3/30
 */
@Getter
@AllArgsConstructor
public enum SqlOracleEnum implements SqlFrameBase {

    /* =============================== 系统语句 ==================================== */
    FIELDS(
            "SELECT * FROM \n" +
                    "(\n" +
                    "SELECT DISTINCT\n" +
                    "\tA.COLUMN_ID,\n" +
                    "\tA.column_name AS " + DbAliasConst.FIELD + ",\n" +
                    "\tA.data_type AS " + DbAliasConst.DATA_TYPE + ",\n" +
                    "\tA.DATA_LENGTH AS " + DtInterface.CHARACTER_LENGTH + ",\n" +
                    "\tA.DATA_PRECISION AS " + DtInterface.NUMERIC_PRECISION + ",\n" +
                    "\tA.DATA_SCALE AS " + DtInterface.NUMERIC_SCALE + ",\n" +
                    "CASE\n" +
                    "\t\tA.nullable \n" +
                    "\t\tWHEN 'N' THEN\n" +
                    "\t\t'0' ELSE '1' \n" +
                    "\tEND AS " + DbAliasEnum.ALLOW_NULL.AS() + ",\n" +
                    "CASE\n" +
                    "\tA.nullable \n" +
                    "\tWHEN 'N' THEN\n" +
                    "\t'1' ELSE '0' \n" +
                    "\tEND AS OLD_PRIMARY,\n" +
                    "\tB.comments AS " + DbAliasConst.FIELD_COMMENT + "\n" +
                    "FROM\n" +
                    "\tuser_tab_columns A,\n" +
                    "\tuser_col_comments B,\n" +
                    "\tall_cons_columns C,\n" +
                    "\tUSER_TAB_COMMENTS D \n" +
                    "WHERE\n" +
                    "\ta.COLUMN_NAME = b.column_name \n" +
                    "\tAND A.Table_Name = B.Table_Name \n" +
                    "\tAND A.Table_Name = D.Table_Name \n" +
                    "\tAND ( A.TABLE_NAME = c.table_name ) \n" +
                    "\tAND A.Table_Name = " + ParamEnum.TABLE.getParamSign() + "\n" +
                    "ORDER BY A.COLUMN_ID ASC\n" +
                    ") A\n" +
                    "LEFT JOIN\n" +
                    "(\n" +
                    "SELECT \n" +
                    "\ta.column_name name,\n" +
                    "\tCASE WHEN a.column_name=t.column_name THEN 1 ELSE 0 END F_PRIMARY_KEY\n" +
                    "FROM \n" +
                    "\tuser_tab_columns a\n" +
                    "LEFT JOIN (SELECT b.table_name,b.column_name FROM user_cons_columns b\n" +
                    "JOIN \n" +
                    "\tuser_constraints c \n" +
                    "ON c.CONSTRAINT_NAME = b.CONSTRAINT_NAME\n" +
                    "WHERE \n" +
                    "\tc.constraint_type   ='P') t ON a.table_name=t.table_name\n" +
                    "WHERE \n" +
                    "\ta.table_name= " + ParamEnum.TABLE.getParamSign() + "\n" +
                    ") B \n" +
                    "ON \n" +
                    "A." + DbAliasConst.FIELD + " = B.NAME"
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(table);
            list.add(table);
        }
    },
    TABLES("SELECT " +
            "a.TABLE_NAME " + DbAliasConst.TABLE_NAME + ", " +
            "b.COMMENTS " + DbAliasConst.TABLE_COMMENT + ", " +
            "a.num_rows " + DbAliasConst.TABLE_SUM +
            "\nFROM user_tables a, user_tab_comments b "
            + "WHERE a.TABLE_NAME = b.TABLE_NAME "
            /*+ "and a.TABLESPACE_NAME='"+ ParamEnum.TABLE_SPACE.getTarget()+"'"*/
    ),
    TABLE(
            TABLES.sqlFrame + " AND a.TABLE_NAME = " + ParamEnum.TABLE.getParamSign()
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(table);
        }
    },
    EXISTS_TABLE(
            "SELECT COUNT(*) AS TOTAL FROM USER_TABLES WHERE USER_TABLES.TABLE_NAME = UPPER(" + ParamEnum.TABLE.getParamSign() + ")"
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(table);
        }
    },


    /* =============================== 定义语句 ==================================== */
    // （Data Definition Language）简称 DDL：用来建立数据库、数据库对象和定义列的命令。包括：create、alter、drop

    CREATE_TABLE    ("<CREATE> <TABLE> {table} <(>\n" +
                    "【{column} {dataType} [[NOT] [NULL]] [<DEFAULT> {defaultValue}] [<PRIMARY KEY>]】" +
                    "<)> "),
    // 添加自增
    CREATE_AUTO_INCREMENT(
            "create sequence {table}_seq\n" +
                    "increment by 1\n" +
                    "START WITH 1\n" +
                    "NOMAXVALUE\n" +
                    "NOCYCLE\n" +
                    "NOCACHE"
    ),
    // 添加自增触发器
    CREATE_AUTO_INCREMENT_TRIGGER(
        "create or replace trigger {table}_trigger \n" +
                "before insert or update on {table}\n" +
                "for each row\n" +
                "  when (new.{autoInc_field} is null)\n" +
                "begin\n" +
                "  select {table}_seq.nextval into:new.{autoInc_field} from dual;\n" +
                "end;"
    ),
    CREATE          ("CREATE TABLE 《schema》.{table}(\n" +
                    "【  " +
                    "1:(PRIMARY KEY ({primaryColumn}))|" +
                    "2:({column} {dataType} [[NOT] [NULL]] [DEFAULT {defaultValue}])" +
                    ",\n】)"
    ),
    DROP_TABLE      ("DROP TABLE {table}"),
    COMMENT_TABLE   ("COMMENT ON TABLE 《schema》.{table} IS '{comment}'"),
    COMMENT_COLUMN  ("COMMENT ON COLUMN 《schema》.{table}.{column} IS '{comment}'"),
    DROP_COLUMN     ("ALTER TABLE 《schema》.{table} DROP COLUMN {column}"),
    ADD_COLUMN      ("ALTER TABLE 《schema》.{table} ADD {column} {dataType}"),
    MODIFY_TYPE     ("ALTER TABLE 《schema》.{table} ALTER COLUMN {column} TYPE {dataType}"),
    ALTER_TABLE     ("ALTER TABLE 《schema》.{oldTable} RENAME TO {newTable}"),

    /* =============================== DML操作语句 ==================================== */
    /* （Data Manipulation Language）简称 DML：用来操纵数据库中数据的命令。包括：select、insert、update、delete。
        注意：有一些Oracle版本中的语法子查询不支持别名关键词 AS */
    /*=============================== ALTER ====================================*/
    ORDER_PAGE      ("SELECT * FROM " +
                    "(SELECT JNPF_TEMP.*, ROWNUM JNPF_RowNo " +
                        "FROM " +
                            "({selectSql}) JNPF_TEMP " +
                        "ORDER BY {orderColumn} [DESC]) JNPF_TAB " +
                    "WHERE JNPF_TAB.JNPF_RowNo BETWEEN {beginIndex} AND {endIndex}"),
    DB_TIME_SQL     ("select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as TIME from dual"),
    INSERT          ("INSERT INTO 《schema》.{table} (【{column},】) VALUES (【{value},】)"),
    /* =============================== 其他 ==================================== */
    /**
     * oracle 时间格式转换
     */
    TO_TIME         ("TO_DATE('{datetime}','YYYY-MM-DD HH24:MI:SS')"),
    /**
     * 查看现有表空间信息
     */
    SELECT_TABLESPACE("SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,round(bytes/(1024*1024),0) total_space FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME"),
    /**
     * 创建临时表空间
     */
    CREATE_TEMP_TABLESPACE("CREATE TEMPORARY TABLESPACE UQSM_TEMP TEMPFILE '/{path}/{tempTablespaceName}.dbf' " +
            "size 8000m autoextend on next 50m maxsize unlimited extent management local;"),
    /**
     * 创建表空间
     */
    CREATE_TABLESPACE("\n" + "CREATE TABLESPACE UQSM_DATA LOGGING DATAFILE '/{path}/{tempTablespaceName}.dbf' " +
            "SIZE 8000m autoextendon next 50M maxsize unlimited extent management local;\n"),
    /**
     * 创建用户
     */
    CREATE_USER     ("CREATE USER {user} IDENTIFIED BY {password} DEFAULT TABLESPACE {tablespace} TEMPORARY TABLESPACE {tempTablespace}"),
    /**
     * 删除用户
     */
    DROP_USER       ("DROP USER {user}"),
    /**
     * 给用户授予权限
     */
    GRANT_ROLE      ("GRANT connect,RESOURCE,dba to {user};"),
    /**
     * 修改用户的密码
     */
    ALTER_USER_PASSWORD("ALTER USER {user} IDENTIFIED BY {password}"),
    ;

    private final String sqlFrame;
    private final String dbEncode = DbBase.ORACLE;

}
